<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class usia_model extends CI_model {

    function __construct() {
        parent::__construct();
        $this->load->database();
    }

    public function getChart($id_kec = 0, $id_kel = 0) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select("kode_usia as kategori_id, nama_usia as kategori, sum(jumlah) as jumlah");
            //$this->db->where('nama_pendidikan is not null');
            $this->db->group_by('kode_usia')
                    ->order_by('kode_usia');
            //$this->db->where('nama_kelurahan is not null');
            $query = $this->db->get('penduduk_per_rentang_usia2');
            return $query->result();
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            //$query=$this->db->get_where('penduduk_per_pekerjaan',$filter)->group_by('kode_kecamatan')->group_by('kode_kelurahan')->order_by('kode_kecamatan')->order_by('kode_kelurahan');
            $this->db->where($filter);
            $this->db->select('kode_usia as kategori_id, nama_usia as kategori, sum(jumlah) as jumlah');
            $this->db->group_by('kode_usia')
                    ->order_by('kode_usia');
            $q = $this->db->get('penduduk_per_rentang_usia2');
            //echo $this->db->last_query();
            return $q->result();
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->where(array('kode_kecamatan' => $filter['kode_kecamatan']));
            $this->db->select('kode_usia as kategori_id, nama_usia as kategori, sum(jumlah) as jumlah');
            $this->db->group_by('kode_usia')
                    ->order_by('kode_usia');
            $q = $this->db->get('penduduk_per_rentang_usia2');
            //echo $this->db->last_query();
            return $q->result();
        }
        //echo $this->db->last_query();
    }

    public function get_jumlah_data_by_usia($kunci, $id_kec, $id_kel) {
        $sql = "select * from master_usia where kode=$kunci";
        $query = $this->db->query($sql);
        $filter = array();
        foreach ($query->result() as $key => $row) {
            $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=$row->umur_min and TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=$row->umur_max)";
            break;
        }

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select count(*) as jumlah from biodata_wni_bgt  "
                . " $s_filter ";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result()[0]->jumlah;
    }

    public function get_data_penduduk_by_usia($kunci, $limit, $halaman, $id_kec, $id_kel) {
        $sql = "select * from master_usia where kode=$kunci";
        $query = $this->db->query($sql);
        $filter = array();
        foreach ($query->result() as $key => $row) {
            $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=$row->umur_min and TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=$row->umur_max)";
            break;
        }

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select *, concat(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE()),' tahun') as umur from biodata_wni_bgt  $s_filter  limit $halaman, $limit ";

        $query = $this->db->query($sql);
        return $query->result();
    }

    public function get_list_usia() {
        $sql = "select kode as kategori_id, concat(umur_min_text,umur_max_text) as kategori from master_usia";
        $query = $this->db->query($sql);
        return $query->result();
    }

    function getChartBalita($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select("sum(jumlah_balita) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kecamatan as kategori_id, "
                    . "nama_kecamatan as kategori");
            $this->db->group_by('kode_kecamatan')
                    ->order_by('kode_kecamatan');
            $query = $this->db->get('penduduk_usia_balita');
            return $query->result();
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select("sum(jumlah_balita) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kelurahan as kategori_id, "
                    . "nama_kelurahan as kategori");
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $this->db->where($filter);
            $q = $this->db->get('penduduk_usia_balita');
            //echo $this->db->last_query();
            return $q->result();
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->where(array('kode_kecamatan' => $filter['kode_kecamatan']));
            $this->db->select("sum(jumlah_balita) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kelurahan as kategori_id, "
                    . "nama_kelurahan as kategori");
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $q = $this->db->get('penduduk_usia_balita');
            return $q->result();
        }
    }

    function getChartSekolah($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select("sum(jumlah_sekolah) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kecamatan as kategori_id, "
                    . "nama_kecamatan as kategori");
            $this->db->group_by('kode_kecamatan')
                    ->order_by('kode_kecamatan');
            $query = $this->db->get('penduduk_usia_sekolah');
            return $query->result();
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select("sum(jumlah_sekolah) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kelurahan as kategori_id, "
                    . "nama_kelurahan as kategori");
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $this->db->where($filter);
            $q = $this->db->get('penduduk_usia_sekolah');
            //echo $this->db->last_query();
            return $q->result();
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->where(array('kode_kecamatan' => $filter['kode_kecamatan']));
            $this->db->select("sum(jumlah_sekolah) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kelurahan as kategori_id, "
                    . "nama_kelurahan as kategori");
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $q = $this->db->get('penduduk_usia_sekolah');
            return $q->result();
        }
    }

    function getChartProduktif($id_kec, $id_kel) {
        $filter = array();
        $filter['kode_kecamatan'] = 0;
        $filter['kode_kelurahan'] = 0;
        if ($id_kec != 0 && strlen($id_kec) > 0 && is_numeric($id_kec)) {
            $filter['kode_kecamatan'] = $id_kec;
        }
        if ($id_kel != 0 && strlen($id_kel) > 0 && is_numeric($id_kel)) {
            $filter['kode_kelurahan'] = $id_kel;
        }
        $query = '';
        if ($filter['kode_kecamatan'] == 0 && $filter['kode_kelurahan'] == 0) {
            $this->db->select("sum(jumlah_produktif) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kecamatan as kategori_id, "
                    . "nama_kecamatan as kategori");
            $this->db->group_by('kode_kecamatan')
                    ->order_by('kode_kecamatan');
            $query = $this->db->get('penduduk_usia_produktif');
            return $query->result();
        } else if ($filter['kode_kecamatan'] != 0 && $filter['kode_kelurahan'] != 0) {
            $this->db->select("sum(jumlah_produktif) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kelurahan as kategori_id, "
                    . "nama_kelurahan as kategori");
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $this->db->where($filter);
            $q = $this->db->get('penduduk_usia_produktif');
            //echo $this->db->last_query();
            return $q->result();
        } else if ($filter['kode_kecamatan'] != 0) {
            $this->db->where(array('kode_kecamatan' => $filter['kode_kecamatan']));
            $this->db->select("sum(jumlah_produktif) as jumlah_balita, "
                    . "sum(jumlah_total) as jumlah_total, kode_kelurahan as kategori_id, "
                    . "nama_kelurahan as kategori");
            $this->db->group_by('kode_kelurahan')
                    ->order_by('kode_kelurahan');
            $q = $this->db->get('penduduk_usia_produktif');
            return $q->result();
        }
    }

    function get_jumlah_data_balita($kunci, $id_kec, $id_kel) {

        $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=0 and "
                . "TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=5)";

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select count(*) as jumlah from biodata_wni_bgt  "
                . " $s_filter ";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result()[0]->jumlah;
    }

    function get_data_balita($kunci, $limit, $halaman, $id_kec, $id_kel) {
        $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=0 and "
                . "TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=5)";

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select *, concat(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE()),' tahun') "
                . "as umur from biodata_wni_bgt  $s_filter  limit $halaman, $limit ";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_jumlah_data_usia_sekolah($kunci, $id_kec, $id_kel) {
        $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=6 and "
                . "TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=15)";

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select count(*) as jumlah from biodata_wni_bgt  "
                . " $s_filter ";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result()[0]->jumlah;
    }

    function get_data_usia_sekolah($kunci, $limit, $halaman, $id_kec, $id_kel) {
        $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=6 and "
                . "TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=15)";

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select *, concat(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE()),' tahun') "
                . "as umur from biodata_wni_bgt  $s_filter  limit $halaman, $limit ";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_data_usia_produktif($kunci, $limit, $halaman, $id_kec, $id_kel) {
        $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=16 and "
                . "TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=30)";

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select *, concat(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE()),' tahun') "
                . "as umur from biodata_wni_bgt  $s_filter  limit $halaman, $limit ";

        $query = $this->db->query($sql);
        return $query->result();
    }

    function get_jumlah_data_usia_produktif($kunci, $id_kec, $id_kel) {
        $filter[] = "(TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())>=16 and "
                . "TIMESTAMPDIFF(YEAR,TGL_LHR,CURDATE())<=30)";

        $sql = "select * from setup_kec where no_kec=$id_kec";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kec=$id_kec";
            break;
        }

        $sql = "select * from setup_kel where no_kel=$id_kel";
        $query = $this->db->query($sql);
        foreach ($query->result() as $key => $row) {
            $filter[] = "no_kel=$id_kel";
            break;
        }
        $s_filter = implode(' and ', $filter);
        if (strlen($s_filter)) {
            $s_filter = ' where ' . $s_filter;
        }
        $sql = "select count(*) as jumlah from biodata_wni_bgt  "
                . " $s_filter ";
        //echo $sql;
        $query = $this->db->query($sql);
        return $query->result()[0]->jumlah;
    }

}
